contents

CTE(Common Table Expression) 는 단일 SELECT, INSERT, UPDATE, 또는 DELETE 문 내에서 참조할 수 있는, 이름을 가진 임시 결과 집합입니다. 이는 WITH 절을 사용하여 정의되며, 복잡한 쿼리를 간단하고, 읽기 쉬우며, 재사용 가능한 논리적 빌딩 블록으로 분해하는 데 도움을 주는 쿼리를 위한 임시 변수처럼 작동합니다.

CTE가 해결하는 문제

CTE 이전에는, 복잡한 쿼리들이 종종 여러 단계로 중첩된 서브쿼리를 포함하거나 임시 테이블 생성을 필요로 했습니다. 이는 읽고, 디버깅하고, 유지보수하기 어려운 코드로 이어졌습니다. CTE는 다음과 같은 몇 가지 주요 문제를 해결합니다.

• 가독성: 복잡한 쿼리를 논리적이고 단계적인 방식으로 구조화할 수 있게 하여 쿼리의 의도를 훨씬 명확하게 만듭니다.
• 재사용성 (쿼리 내에서): CTE는 동일한 메인 쿼리 내에서 여러 번 참조될 수 있어, 동일한 로직을 반복해서 작성할 필요가 없습니다.
• 재귀: 이것이 CTE의 핵심 기능입니다. CTE는 자기 자신을 참조할 수 있어, 조직도나 자재 명세서(BOM)와 같은 계층적 데이터를 탐색할 수 있게 해줍니다. 이는 간단한 서브쿼리로는 불가능합니다.

핵심 문법: WITH 절 📜

표준(비재귀적) CTE는 쿼리의 시작 부분에 정의됩니다.

구조:

WITH CTE이름 (컬럼1, 컬럼2, ...) AS (
    -- CTE 쿼리 정의 부분
    SELECT ... FROM ... WHERE ...
)
-- CTE를 사용하는 메인 쿼리 부분
SELECT ... FROM CTE이름 ...;

예시:

employees와 orders 테이블이 있다고 가정해 봅시다. 매출이 10만 달러 이상인 직원들의 이름을 찾고 싶습니다.

CTE 미사용 (서브쿼리 사용):

SELECT
    e.first_name,
    e.last_name,
    s.total_sales
FROM
    employees e
JOIN
    (SELECT employee_id, SUM(amount) AS total_sales
     FROM orders
     GROUP BY employee_id) AS s ON e.employee_id = s.employee_id
WHERE
    s.total_sales > 100000;

CTE 사용:

WITH EmployeeSales AS (
    -- 먼저, 임시 결과 집합을 정의합니다: 직원별 총 매출
    SELECT
        employee_id,
        SUM(amount) AS total_sales
    FROM
        orders
    GROUP BY
        employee_id
)
-- 이제, 이 간단하고 이름이 붙은 결과 집합을 메인 쿼리에서 사용합니다.
SELECT
    e.first_name,
    e.last_name,
    es.total_sales
FROM
    employees e
JOIN
    EmployeeSales es ON e.employee_id = es.employee_id
WHERE
    es.total_sales > 100000;

보시다시피, CTE 버전은 로직을 EmployeeSales라는 명확한 빌딩 블록으로 분리하여 전체 쿼리를 훨씬 읽고 이해하기 쉽게 만듭니다.

재귀 CTE 🔄

이것이 CTE가 진정으로 강력해지는 지점입니다. 재귀 CTE는 반복적인 작업을 수행하기 위해 자기 자신을 참조하는 CTE입니다. 계층적 데이터를 쿼리하는 데 완벽합니다.

재귀 CTE는 반드시 두 부분으로 구성되어야 합니다.

1. 앵커 멤버 (Anchor Member): 재귀의 기본 케이스입니다. 한 번만 실행되며 초기 행 집합을 제공하는 SELECT 문입니다.
2. 재귀 멤버 (Recursive Member): 반복적인 부분입니다. CTE 자체를 참조하여 그 출력을 다른 테이블과 조인하는 SELECT 문입니다. 이 부분은 더 이상 행을 반환하지 않을 때까지 반복적으로 실행됩니다.

이 두 멤버는 UNION ALL로 결합됩니다.

예시: 직원 조직도

각 직원이 다른 직원을 가리키는 manager_id를 가진 employees 테이블을 상상해 봅시다. 특정 관리자 아래의 전체 보고 체계를 찾아보겠습니다.

-- RECURSIVE 키워드는 PostgreSQL에서는 필수이지만, SQL Server에서는 선택 사항입니다.
WITH RECURSIVE OrgChart AS (
    -- 1. 앵커 멤버: 시작점(최상위 관리자)을 찾습니다.
    SELECT
        employee_id,
        first_name,
        manager_id,
        0 AS hierarchy_level -- 계층 레벨 0에서 시작
    FROM
        employees
    WHERE
        employee_id = 101 -- 시작 관리자

    UNION ALL

    -- 2. 재귀 멤버: employees 테이블을 CTE 자신과 조인합니다.
    SELECT
        e.employee_id,
        e.first_name,
        e.manager_id,
        oc.hierarchy_level + 1 -- 한 단계 내려갈 때마다 레벨 증가
    FROM
        employees e
    JOIN
        OrgChart oc ON e.manager_id = oc.employee_id -- 이것이 재귀 조인입니다.
)
-- 3. 메인 쿼리: CTE에서 최종 결과를 선택합니다.
SELECT * FROM OrgChart ORDER BY hierarchy_level;

이 쿼리는 직원 101로 시작하여, 101에게 보고하는 모든 직원을 찾고, 그 다음에는 그들에게 보고하는 모든 직원을 찾는 식으로 전체 계층이 탐색될 때까지 계속됩니다.

CTE vs. 대안

references